package me.zhengjie.modules.workorder.repository;

import me.zhengjie.modules.workorder.domain.TWorkOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.ArrayList;
import java.util.List;

/**
* @author zcw
* @date 2019-08-13
*/
public interface TWorkOrderRepository extends JpaRepository<TWorkOrder, Long>, JpaSpecificationExecutor {

    @Query(value="select count(id) as count, status from TWorkOrder group by status")
    public List<?> getWorkOrderAggByStatus();

    @Query(value="select count(id) as count, status from TWorkOrder where supplier=:serviceproviderid  and engineer=:engineer group by status")
    public List<?> getWorkOrderAggByStatus(@Param("serviceproviderid") Long serviceproviderid,
                                           @Param("engineer") Long engineer);

    @Query(value="SELECT * from t_work_order t where t.order_source = :orderSource and t.source_id = :sourceId",nativeQuery = true)
	public List<TWorkOrder> findOrderByOrderSource(@Param("orderSource") Integer orderSource,@Param("sourceId") Long sourceId);

    public TWorkOrder findFirstByOrderCode(String ordderCode);

    @Modifying
    @Query(value="update t_work_order set customer_name=:customer_name where customer_id=:customer_id",nativeQuery = true)
    public Integer updateCustomerName(@Param("customer_id") Long customer_id, @Param("customer_name") String customer_ame);

    @Modifying
    @Query(value="update t_work_order set supplier_name=:supplier_name where supplier=:supplier",nativeQuery = true)
    public Integer updateSupplierName(@Param("supplier") Long supplier, @Param("supplier_name") String supplier_name);


    //获取可接单的订单,处于dispatch状态
    @Query(value="select * from t_work_order p " +
            " where p.supplier=:supplier and status=200 " +
            " and (:major is null or p.major=:major)" +
            " and ( p.level in :level)" +
            " and (:country is null or p.country=:country)" +
            " and (:province is null or p.province=:province)" +
            " and (:city is null or p.city=:city)" +
            " and (:county is null or ((p.filter_option='区' and p.county=:county) or p.filter_option='市')) ",
            nativeQuery = true)
    public Page<TWorkOrder> queryAcceptableWorkorder(@Param("major") String major,
                                                     @Param("level") ArrayList<String> level,
                                                     @Param("country") String country,
                                                     @Param("province") String province,
                                                     @Param("city") String city,
                                                     @Param("county") String county,
                                                     @Param("supplier") Long supplier,
                                                     Pageable pageable);

    //获取可接单的订单,处于dispatch状态
    @Query(value="select count(1) from t_work_order p " +
            " where p.supplier=:supplier and status=200 " +
            " and (:major is null or p.major=:major)" +
            " and ( p.level in :level)" +
            " and (:country is null or p.country=:country)" +
            " and (:province is null or p.province=:province)" +
            " and (:city is null or p.city=:city)" +
            " and (:county is null or ((p.filter_option='区' and p.county=:county) or p.filter_option='市')) ",
            nativeQuery = true)
    public Long countAcceptableWorkorder(@Param("major") String major,
                             @Param("level") ArrayList<String> level,
                             @Param("country") String country,
                             @Param("province") String province,
                             @Param("city") String city,
                             @Param("county") String county,
                             @Param("supplier") Long supplier);



    @Query(value="select p.id, p.order_code as orderCode," +
            "p.customer_name as customerName," +
            "p.supplier_name as supplierName," +
            "p.major,p.level," +
            "p.evaluate_time as evaluateTime," +
            "p.actual_service_time as realServiceTime," +
            "p.customer_cost as customerCost," +
            "t.task_title as taskTitle,t.task_addr as taskAddr" +
            " from t_work_order as p, t_tasks t " +
            " where p.status in (700,800) " +
            " and p.order_source=:orderSource and p.source_id=t.id" +
            " and (:customerName is null or p.customer_name like concat('%', :customerName ,'%'))" +
            " and (:supplierName is null or p.supplier_name like concat('%', :supplierName ,'%'))" +
            " and (:evaluateTimeBegin is null or p.evaluate_time>=:evaluateTimeBegin)" +
            " and (:evaluateTimeEnd is null or p.evaluate_time<:evaluateTimeEnd)" +
            " and (:orderCode is null or p.order_code like concat('%', :orderCode,'%')) ",
            countProjection = "p.id",
            nativeQuery = true)
    public Page<java.util.Map<?,?>> queryCustomerWorkorderReport(@Param("orderSource") Integer orderSource,
                                                      @Param("evaluateTimeBegin") String evaluateTimeBegin,
                                                      @Param("evaluateTimeEnd") String evaluateTimeEnd,
                                                      @Param("customerName") String customerName,
                                                      @Param("supplierName") String supplierName,
                                                      @Param("orderCode") String orderCode,
                                                      Pageable pageable);


    @Query(value="select p.id, p.order_code as orderCode," +
            "p.customer_name as customerName," +
            "p.supplier_name as supplierName," +
            "p.major,p.level," +
            "p.evaluate_time as evaluateTime," +
            "p.actual_service_time as realServiceTime," +
            "p.customer_cost as customerCost," +
            "t.task_title as taskTitle,t.task_addr as taskAddr" +
            " from t_work_order as p, t_tasks t " +
            " where p.status in (700,800) " +
            " and p.order_source=:orderSource and p.source_id=t.id" +
            " and (:customerName is null or p.customer_name like concat('%', :customerName ,'%'))" +
            " and (:supplierName is null or p.supplier_name like concat('%', :supplierName ,'%'))" +
            " and (:evaluateTimeBegin is null or p.evaluate_time>=:evaluateTimeBegin)" +
            " and (:evaluateTimeEnd is null or p.evaluate_time<:evaluateTimeEnd)" +
            " and (:orderCode is null or p.order_code like concat('%', :orderCode,'%')) ",
            countProjection = "p.id",
            nativeQuery = true)
    public List<java.util.Map<?,?>> queryCustomerWorkorderReport(@Param("orderSource") Integer orderSource,
                                                                 @Param("evaluateTimeBegin") String evaluateTimeBegin,
                                                                 @Param("evaluateTimeEnd") String evaluateTimeEnd,
                                                                 @Param("customerName") String customerName,
                                                                 @Param("supplierName") String supplierName,
                                                                 @Param("orderCode") String orderCode);
}